﻿CREATE PROCEDURE [maint].[UpdateServerConfig]
@DbVersion NVARCHAR (7), @BackupPath NVARCHAR (256), @SMTPServer NVARCHAR (256), @TraceFileFolder NVARCHAR (256), @ReportSnapshotScript NVARCHAR (256), @MaintenanceDiagnostics BIT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @RC int;
	SET @RC = 0;

	--Enable DBADb specific server config requirements
	EXEC sp_configure 'clr enabled', 1 RECONFIGURE
	EXEC sp_configure 'show advanced options', 1 RECONFIGURE
	EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGURE

	--Replace variable placeholders with values
	DECLARE @SQLDataRoot nvarchar(256), @ServerName sysname;
	EXEC [util].[GetSQLDataRoot] @SQLDataRoot OUTPUT;
	SET @ServerName = REPLACE(CAST(@@SERVERNAME AS nvarchar(128)),'\','$');
	
	SET @BackupPath = REPLACE(REPLACE(@BackupPath,'#(SQLDataRoot)',@SQLDataRoot),'#(ServerName)',@ServerName);	
	SET @TraceFileFolder = REPLACE(REPLACE(@TraceFileFolder,'#(SQLDataRoot)',@SQLDataRoot),'#(ServerName)',@ServerName);
	SET @ReportSnapshotScript = REPLACE(REPLACE(@ReportSnapshotScript,'#(SQLDataRoot)',@SQLDataRoot),'#(ServerName)',@ServerName);

	--Refresh config table
	CREATE TABLE #CONFIGINSTANCEDATA(
		[Name] [nvarchar] (256) NOT NULL
		,[Value] [sql_variant] NOT NULL
	)

	INSERT INTO #CONFIGINSTANCEDATA VALUES (N'DbVersion', @DbVersion)
	INSERT INTO #CONFIGINSTANCEDATA VALUES (N'BackupPath', @BackupPath)
	INSERT INTO #CONFIGINSTANCEDATA VALUES (N'SMTPServer', @SMTPServer)
	INSERT INTO #CONFIGINSTANCEDATA VALUES (N'TraceFileFolder', @TraceFileFolder)
	INSERT INTO #CONFIGINSTANCEDATA VALUES (N'ReportSnapshotScript', @ReportSnapshotScript)
	INSERT INTO #CONFIGINSTANCEDATA VALUES (N'MaintenanceDiagnostics', @MaintenanceDiagnostics)

	INSERT [config].[InstanceSettings] ([Name],[Value])
	SELECT
		tmp.[Name] COLLATE database_default
		,tmp.[Value]
	FROM #CONFIGINSTANCEDATA AS tmp
	LEFT JOIN [config].[InstanceSettings] AS tbl
		ON (tbl.[Name] = tmp.[Name] COLLATE database_default)
	WHERE (tbl.[Name] IS NULL)

	UPDATE tbl
	SET
		[Value] = tmp.[Value]
	FROM [config].[InstanceSettings] AS tbl
	INNER JOIN #CONFIGINSTANCEDATA AS tmp
		ON (tmp.[Name] COLLATE database_default = tbl.[Name])

	DROP TABLE #CONFIGINSTANCEDATA;

END